<?php

namespace app\index\dao;

use app\common\model\Consulting;
use app\common\model\Member;
use app\common\model\Trade;
use app\index\traits\MatchRecorderTrait;
use ShidianUtil\Thinkphp\Dao\DaoTrait;
use ShidianUtil\Utils\StringUtil;
use ShidianUtil\Utils\TimeUtil;
use think\Collection;

/**
 * MemberDao 数据访问对象
 * Class MemberDao
 * @package app\dao
 * @method Member findByPK(?int $id, $field = true)
 * @method \think\Collection selectAll(?int $page, ?int $page_size, $field = true)
 * @method Member insertActive(array $params, array $allow_field = [])
 * @method Member updateActive(Member $model, array $params, array $allow_field = [])
 * @method Member findAndUpdateActive(int $id, array $params, array $allow_field = [])
 * @method Member updateActiveByClosure(\Closure $closure, array $params, array $allow_field = [])
 * @method void deleteModel(Member $model)
 * @method void deleteById(int|array|\Closure $id, bool $force = false)
 * @link https://www.kancloud.cn/manual/thinkphp6_0/1037579
 * @link https://www.kancloud.cn/manual/think-orm/1257998
 * @link https://www.kancloud.cn/thinkphp/master-database-and-model/265551
 */
class MemberDao
{
    use DaoTrait;
    use MatchRecorderTrait;

    /**
     * @var Member
     */
    private $baseModel;

    /**
     * MemberDao constructor.
     */
    public function __construct()
    {
        $this->baseModel = Member::class;
    }

    /**
     * 统计关注了公众号的律师数量
     * @return int
     */
    public function countFollowedWechatLawyer(): int
    {
        return Member::where('type', '1')
            ->where('followedWechat', '1')
            ->count();
    }

    /**
     * 前端用户模型
     * @param $id
     * @return Member|null
     */
    public function findByLogin($id): ?Member
    {
        return Member::where('id', $id)
            ->field(true)
            ->with([
                'province' => fn($query) => $query->shortArea(),
                'city' => fn($query) => $query->shortArea(),
            ])
            ->append(['headImg', 'headImgThumb', 'cropImg', 'axbPhoneShowStr', 'isPersonnel', 'memberPt'])
            ->shield()
            ->find();
    }

    /**
     * 查询律师排行榜
     * @param $rankType
     * @param $page
     * @param $page_size
     * @return Collection
     */
    public function selectLawyersRank($rankType, $page, $page_size): Collection
    {
        $tableName = Member::getTable();

        return $this->queryRank($tableName, $rankType)
            ->where("$tableName.type", '1')
            ->where("$tableName.subType", '0')
            ->whereNotNull('lr.rank')
            ->where('status', '1')
            ->order('lr.rank', 'ASC')
            ->order('id', 'ASC')
            ->paging($page, $page_size)
            ->select();
    }

    public function findLawyersRank($id, $rankType): ?Member
    {
        $tableName = Member::getTable();

        return $this->queryRank($tableName, $rankType)
            ->where("$tableName.id", $id)
            ->where("$tableName.type", '1')
            ->where("$tableName.subType", '0')
            ->where('status', '1')
            ->find();
    }

    /**
     * 律师排行榜查询
     * @param $tableName
     * @param $rankType
     * @return Member
     */
    private function queryRank($tableName, $rankType)
    {
        return Member::leftJoin('lawyer_rank lr', "$tableName.id=lr.lawyerId and lr.rankType=:rankType", ['rankType' => $rankType])
            ->tableField(['id', 'nickname', 'headImgUrl', 'cropImgUrl'], $tableName)
            ->tableField(['rank', 'answerNumber', 'commentLevel'], 'lr')
            ->with(['memberInfo' => function ($query) {
                $query->field([
                    'memberId',
                    'realname',
                    'practiceTime',
                    'answerNumber',
                    'commentLevel',
                ])->with([
                    'categories' => fn($query) => $query->shortCategory(),
                ])->withAttr([
                    'commentLevel' => fn($value) => float_format(max($value, 4.9), 1),
                ])->append(['todo', 'practiceTimeStr']);

            }])
            ->append(['headImg', 'headImgThumb', 'cropImg'])
            ->hidden(['headImgUrl']);
    }

    /**
     * 首页律师
     * @param $cityId
     * @param int $number
     * @return Collection
     */
    public function selectLawyersHome($cityId, $provinceId, int $number): Collection
    {
        $tableName = Member::getTable();

        $query = Member::leftJoin('member_info mi', "$tableName.id=mi.memberId")
            ->where("$tableName.type", '1')
            ->where("$tableName.subType", '0')
            ->where("$tableName.status", '1')
            ->where("$tableName.muted", '0')
            ->where("$tableName.followedWechat", 1)
            ->where('mi.medal', 1)
            ->having('n', 1)
            ->tableField(['id', 'nickname', 'realname', 'headImgUrl', 'cropImgUrl', 'type'], $tableName)
            ->field(['IF(mi.rushTimes>50, "1", "0")' => 'n'])
            ->with(['memberInfo' => function ($query) {
                $query->field([
                    'memberId',
                    'practiceTime',
                    'commentLevel',
                    'practicingLawFirm',
                    'realname',
                    'professionalField',
                    'rushTimes',
                    'answerNumber',
                    'medal',
                    'servedMemberNumber',
                    'provinceId',
                    'cityId',
                ])->with([
                    'ranks' => fn($query) => $query->where('rank', '<=', 20)->append(['rankStr']),
                    'categories' => fn($query) => $query->shortCategory(),
                    'province' => fn($query) => $query->shortArea(),
                    'city' => fn($query) => $query->shortArea(),
                ])->withAttr([
                    'commentLevel' => fn($value) => float_format(max($value, 4.9), 1),
                ])->append(['todo', 'practiceTimeStr']);

            }])
            ->append(['headImg', 'headImgThumb', 'cropImg', 'showName'])
            ->hidden(['headImgUrl']);

        // 优先同城市
        if (is_id($cityId)) {
            $query->orderValues('mi.cityId', [$cityId]);
        }

        // 然后同省份
        if (is_id($provinceId)) {
            $query->orderValues('mi.provinceId', [$provinceId]);
        }

        $collection = $query->order('id', 'DESC')
            ->limit($number)
            ->select();

        $this->wrapCollectionRankStr($collection);

        return $collection;
    }

    /**
     * 打官司推荐律师
     * @param $categoryId
     * @param $cityId
     * @param $provinceId
     * @param int $number
     * @return Collection
     */
    public function selectLawyersLawsuit($categoryId, $cityId, $provinceId, int $number): Collection
    {
        $tableName = Member::getTable();

        $query = Member::leftJoin('member_info mi', "$tableName.id=mi.memberId")
            // ->leftJoin('member_category_rel mcr', "$tableName.id=mcr.memberId")
            ->where("$tableName.type", '1')
            ->where("$tableName.subType", '0')
            ->where("$tableName.status", '1')
            ->where("$tableName.muted", '0')
            ->tableField(['id', 'nickname', 'realname', 'headImgUrl', 'cropImgUrl', 'type'], $tableName)
            ->with(['memberInfo' => function ($query) use ($categoryId) {
                $query->field([
                    'memberId',
                    'practiceTime',
                ])->with([
                    'categories' => fn($query) => $query->shortCategory()->orderValues('id', [$categoryId]),
                ])->append(['practiceTimeStr']);

            }])
            ->append(['headImg', 'headImgThumb', 'cropImg', 'showName'])
            ->hidden(['headImgUrl']);

        $query->group("$tableName.id");

        // 擅长领域
        // $query->orderValues('mcr.categoryId', [$categoryId]);

        // 金牌优先
        $query->order('mi.medal', 'DESC');

        // 优先同城市
        if (is_id($cityId)) {
            $query->orderValues('mi.cityId', [$cityId]);
        }

        // 然后同省份
        if (is_id($provinceId)) {
            $query->orderValues('mi.provinceId', [$provinceId]);
        }

        return $query->order("$tableName.id", 'DESC')
            ->limit($number)
            ->select();
    }

    /**
     * 获取随机匹配律师
     * @param $cityId
     * @param $provinceId
     * @param $excludedLawyerIds
     * @param $excludedLawyerPhones
     * @return Member|null
     */
    public function findLawyerRand($cityId, $provinceId, $excludedLawyerIds, $excludedLawyerPhones)
    {
        // 本地优先 ok
        // 咨询人数小于50人优先推送 ok
        // 每个律师推送一天不超过3个
        // 仅推送金牌律师。有关注公众号 ok

        //读取今天已经推送了3个（或大于3个）的律师，把他们排在最后
        $botIds = $this->getLawyerIdsMatchMoreThan();

        $tableName = Member::getTable();

        // 子查询
        $subSql = Consulting::whereRaw("lawyerId=$tableName.id")
            ->field(['count(*)'])
            ->buildSql();

        // 查询一个律师
        $query = Member::leftJoin('member_info mi', "$tableName.id=mi.memberId")
            ->where("$tableName.type", '1')
            ->where("$tableName.subType", '0')
            ->where("$tableName.status", '1')
            ->where("$tableName.muted", '0')
            ->where('mi.medal', '1')
            ->where("$tableName.followedWechat", '1')
            ->when(!empty($excludedLawyerIds), fn($query) => $query->whereNotIn("$tableName.id", $excludedLawyerIds))
            ->when(!empty($tests), fn($query) => $query->whereNotIn("$tableName.phone", $excludedLawyerPhones))
            ->tableField(true, $tableName)
            ->field([
                'IF(mi.rushTimes>50, "1", "0")' => 'n',
                $subSql => 'c',
            ]);

        if (!empty($botIds)) {
            $query->orderField("$tableName.id", $botIds, 'ASC');
        }

        if (is_id($cityId)) {
            $query->orderValues('mi.cityId', [$cityId]);
        }
        if (is_id($provinceId)) {
            $query->orderValues('mi.provinceId', [$provinceId]);
        }

        $query->order('n', 'ASC')
            ->order('c', '');

        return $query->orderRand()->find();
    }

    // 律师排行榜
    private function wrapCollectionRankStr(Collection $collection)
    {
        $collection->each(function (Member $lawyer) {
            $this->wrapRankStr($lawyer);
        });
    }

    private function wrapRankStr(Member $lawyer)
    {
        if ($lawyer->memberInfo) {
            $rankStr = '';
            if (!$lawyer->memberInfo->ranks->isEmpty()) {
                $rankStr = collection_rand($lawyer->memberInfo->ranks)->rankStr;
            }
            $lawyer->memberInfo->rankStr = $rankStr;
        }
    }

    /**
     * 律师列表
     * @param $cityId
     * @param $categoryId
     * @param $kw
     * @param $order
     * @param $page
     * @param $page_size
     * @return Collection
     */
    public function selectLawyers($cityId, $categoryId, $kw, $order, $page, $page_size): Collection
    {
        $tableName = Member::getTable();

        $query = Member::leftJoin('member_info mi', "$tableName.id=mi.memberId")
            ->when(is_id($cityId), fn($query) => $query->where('mi.cityId', $cityId))
            ->when(is_id($categoryId), function ($query) use ($categoryId) {
                $query->leftJoin('member_category_rel mcr', 'mi.memberId=mcr.memberId')
                    ->where('mcr.categoryId', $categoryId);
            })
            ->where('mi.medal', 1)
            // ->when(is_numeric($medal), fn($query) => $query->where('mi.medal', $medal))
            ->where("$tableName.type", '1')
            ->where("$tableName.subType", '0')
            ->where("$tableName.checkStatus", '1')
            ->where("$tableName.status", '1')
            ->where('muted', '0');

        // 排除测试账号
        if (not_blank($kw)) {

            if (StringUtil::equals($kw, 'test')) {
                $query->where('id', '248208');
            } else {
                $query->search("$tableName.realname", $kw)
                    ->where("$tableName.id", '<>', '248208');
            }
        } else {
            $query->where("$tableName.id", '<>', '248208');
        }

        $query->tableField(['id', 'nickname', 'headImgUrl', 'cropImgUrl'], $tableName)
            ->with([
                'memberInfo' => function ($query) {
                    $query->field([
                        'memberId',
                        'realname',
                        'commentLevel',
                        'medal',
                        'practicingLawFirm',
                        'practiceTime',
                        'rushTimes',
                        'servedMemberNumber',
                    ])->with([
                        'categories' => fn($query) => $query->shortCategory(),
                    ])->withAttr([
                        'commentLevel' => fn($value) => float_format(max($value, 4.9), 1),
                    ])->append(['todo', 'practiceTimeStr']);
                }
            ])
            ->append(['headImg', 'headImgThumb', 'cropImg'])
            ->hidden(['headImgUrl']);

        // 按咨询人数倒序，按执业年限倒序，按星级倒序，优先展示在线律师，如果数据相同按咨询人数，如果还是相同就随机
        if ($order == 'servedMemberNumber') {
            // 智能排序
            // $query->order("$tableName.followedWechat", 'DESC')
            //     ->order('mi.medal', 'DESC');
            // ->order("$tableName.matchStatus", 'DESC');
            if (is_id($cityId)) {
                $query->orderValues('mi.cityId', [$cityId]);
            }
            $query->order('mi.rushTimes', 'DESC');
        } elseif ($order == 'practiceTime') {
            $query->order('mi.practiceTime', 'DESC')
                // ->order("$tableName.matchStatus", 'DESC')
                ->order('mi.rushTimes', 'DESC')
                ->order('mi.medal', 'DESC');
        } elseif ($order == 'commentLevel') {
            $query->order('mi.commentLevel', 'DESC')
                // ->order("$tableName.matchStatus", 'DESC')
                ->order('mi.rushTimes', 'DESC')
                ->order('mi.medal', 'DESC');
        }

        return $query->order('id', 'DESC')
            ->paging($page, $page_size)
            ->group("$tableName.id")
            ->select();
    }

    // 查询某案件类型的律师
    public function selectLawyersByCategory($categoryId, array $exclude, $number): Collection
    {
        $tableName = Member::getTable();
        $query = Member::leftJoin('member_info mi', "$tableName.id=mi.memberId")
            ->leftJoin('member_category_rel mcr', "$tableName.id=mcr.memberId");

        if (is_id($categoryId)) {
            $query->where('mcr.categoryId', $categoryId);
        }

        return $query->where("$tableName.type", '1')
            ->where("$tableName.subType", '0')
            ->where("$tableName.checkStatus", '1')
            ->where("$tableName.status", '1')
            ->where('muted', '0')
            ->when(!empty($exclude), fn($query) => $query->whereNotIn("$tableName.id", $exclude))
            ->tableField(['id', 'nickname', 'realname', 'headImgUrl', 'cropImgUrl', 'type'], $tableName)
            ->with([
                'memberInfo' => function ($query) {
                    $query->field([
                        'memberId',
                        'realname',
                        'commentLevel',
                        'practicingLawFirm',
                        'practiceTime',
                        'rushTimes',
                        'servedMemberNumber',
                    ])->with([
                        'categories' => fn($query) => $query->shortCategory(),
                    ])->withAttr([
                        'commentLevel' => fn($value) => float_format(max($value, 4.9), 1),
                    ])->append(['practiceTimeStr']);
                }
            ])
            ->append(['headImg', 'headImgThumb', 'cropImg', 'showName'])
            ->hidden(['headImgUrl'])
            ->order('mi.medal', 'DESC')
            ->order("$tableName.matchStatus", 'DESC')
            ->order('mi.servedMemberNumber', 'DESC')
            ->orderRand()
            ->limit($number)
            ->group("$tableName.id")
            ->select();
    }

    /**
     * 查询律师主页
     * @param $id
     * @return Member|null
     */
    public function findLawyerHome($id): ?Member
    {
        return Member::where('id', $id)
            ->where('type', 1)
            ->whereIn('status', [0, 1])
            ->field(['id', 'nickname', 'headImgUrl', 'cropImgUrl', 'matchStatus'])
            ->with([
                'memberInfo' => function ($query) {
                    $query->field([
                        'memberId',
                        'indexPic',
                        'realname',
                        'medal',
                        'practiceTime',
                        'practicingLawFirm',
                        'desc',
                        'introduce',
                        'provinceId',
                        'cityId',
                        'rushTimes',
                        'servedMemberNumber',
                        'commentMemberNumber',
                        'askAvgTimes',
                        'commentLevel',
                    ])->with([
                        'professionalRank' => fn($query) => $query->where('rankType', 'professional')->append(['rankStr']),
                        'commentRank' => fn($query) => $query->where('rankType', 'comment')->append(['rankStr']),
                        'categories' => fn($query) => $query->shortCategory(),
                        'province' => fn($query) => $query->shortArea(),
                        'city' => fn($query) => $query->shortArea(),
                        'comments' => fn($query) => $query->order('id', 'DESC')->limit(10),
                    ])->withAttr([
                        'commentLevel' => fn($value) => float_format(max($value, 4.9), 1),
                    ])->append(['indexPicImg', 'practiceTimeStr'])
                        ->hidden(['indexPic']);
                }
            ])
            ->append(['headImg', 'headImgThumb', 'cropImg'])
            ->find();
    }

    /**
     * 工作台律师详情
     * @param $id
     * @return Member|null
     */
    public function findLawyerWorkbench($id): ?Member
    {
        $startTime = TimeUtil::stamp(TimeUtil::DAY);
        $endTime = TimeUtil::stamp(TimeUtil::DAY, $startTime, false);

        return Member::where('id', $id)
            ->where('type', 1)
            ->whereIn('status', [0, 1])
            ->field(['id', 'nickname', 'realname', 'headImgUrl', 'cropImgUrl', 'matchStatus', 'type'])
            ->with([
                'memberInfo' => function ($query) {
                    $query->field([
                        'memberId',
                        'answerNumber',
                        'askAvgTimes',
                        'commentLevel',
                        'medal',
                    ]);
                }
            ])
            ->withCount([
                'pendingConsulting' => fn($query) => $query->where('status', 1)->where('rushStatus', 1)->where('isAsked', 1),
                'lawyerTodayConsulting' => fn($query) => $query->where('rushStatus', 2)->where('created', '>=', $startTime)->where('created', '<=', $endTime),
                'lawyerTodayLawCases' => fn($query) => $query->wherePivot('tradeType', TRADE_LAS_CASE)
                    ->wherePivot('payStatus', Trade::PAID)
                    ->wherePivot('refundStatus', Trade::NOTREFUND)
                    ->wherePivot('created', '>=', $startTime)
                    ->wherePivot('created', '<=', $endTime),
            ])
            ->withSum([
                'transfer' => fn($query) => $query->where('payMethod', PAYMENT_ACCOUNT)
                    // 服务补贴，律师分账，邀请人分账
                    ->whereIn('transferType', ['subsidy', 'shareLawyer', 'shareInviter'])
                    ->where('transferStatus', '2')
                    ->where('created', '>=', $startTime)
                    ->where('created', '<=', $endTime),
            ], 'totalMoney')
            ->withAttr([
                'transfer_sum' => fn($value, $data) => $value ?: 0,
            ])
            ->append(['showName', 'headImg', 'headImgThumb', 'cropImg'])
            ->find();
    }

    /**
     * 查询律师主页设置
     * @param $id
     * @return Member|null
     */
    public function findLawyerHomeSetting($id): ?Member
    {
        return Member::where('id', $id)
            ->where('type', 1)
            ->whereIn('status', [0, 1])
            ->field(['id', 'nickname', 'headImgUrl', 'cropImgUrl'])
            ->with([
                'memberInfo' => function ($query) {
                    $query->field([
                        'memberId',
                        'indexPic',
                        'desc',
                        'introduce',
                        'realname',
                        'practiceNumber',
                        'practicingLawFirm',
                        'certificatePic',
                    ])->with([
                        'categories' => fn($query) => $query->shortCategory(),
                    ])->append(['indexPicImg', 'certificatePicImg']);
                }
            ])
            ->append(['headImg', 'headImgThumb', 'cropImg'])
            ->find();
    }

    // 卡片消息用的
    public function findCardMessageOfLawyer($id)
    {
        $record = Member::where('id', $id)
            ->whereIn('status', [0, 1])
            ->field(['id', 'nickname', 'headImgUrl', 'cropImgUrl'])
            ->with(['memberInfo' => function ($query) {
                $query->field([
                    'memberId',
                    'practiceTime',
                    'commentLevel',
                    'practicingLawFirm',
                    'realname',
                    'professionalField',
                    'answerNumber',
                    'medal',
                ])->with([
                    'ranks' => fn($query) => $query->where('rank', '<=', 20)->append(['rankStr']),
                    'categories' => fn($query) => $query->shortCategory(),
                ])->withAttr([
                    'commentLevel' => fn($value) => float_format(max($value, 4.9), 1),
                ])->append(['todo', 'practiceTimeStr']);
            }])
            ->append(['headImg', 'headImgThumb', 'cropImg'])
            ->find();

        $this->wrapRankStr($record);

        return $record;
    }

    // 卡片消息用的
    public function selectCardMessageOfLawyer($ids)
    {
        $collection = Member::whereIn('id', $ids)
            ->whereIn('status', [0, 1])
            ->field(['id', 'nickname', 'headImgUrl', 'cropImgUrl'])
            ->with(['memberInfo' => function ($query) {
                $query->field([
                    'memberId',
                    'practiceTime',
                    'commentLevel',
                    'practicingLawFirm',
                    'realname',
                    'professionalField',
                    'answerNumber',
                    'medal',
                ])->with([
                    'ranks' => fn($query) => $query->where('rank', '<=', 20)->append(['rankStr']),
                    'categories' => fn($query) => $query->shortCategory(),
                ])->withAttr([
                    'commentLevel' => fn($value) => float_format(max($value, 4.9), 1),
                ])->append(['todo', 'practiceTimeStr']);
            }])
            ->append(['headImg', 'headImgThumb', 'cropImg'])
            ->select();

        $this->wrapCollectionRankStr($collection);

        return $collection;
    }

    /**
     * 查询某用户邀请的用户列表
     * @param $memberId
     * @param $limit
     * @return Member[]|array|Collection
     */
    public function selectInviterMembers($memberId, $limit = 10): Collection
    {
        $tableName = Member::getTable();

        return Member::leftJoin('member_invite mi', "$tableName.id=mi.memberIdInvite and mi.memberId=:memberId" , ['memberId' => $memberId])
            ->where('mi.memberId', $memberId)
            ->where('mi.status', '1')
            ->tableField(['id', 'type', 'nickname', 'realname', 'headImgUrl', 'cropImgUrl', 'followedWechat'], $tableName)
            ->withSum([
                'trades' => fn($query) => $query->where('payStatus', Trade::PAID)
                    ->where('refundStatus', '<>', Trade::REFUNDED)
                    ->where('settleStatus', 'SETTLED')
                    ->where('settleInviterId', $memberId)
                    ->where('tradeType', TRADE_TOKEN_RECHARGE),
            ], 'payMoney')
            ->withSum([
                'settledTrades' => fn($query) => $query->where('payStatus', Trade::PAID)
                    ->where('refundStatus', '<>', Trade::REFUNDED)
                    ->where('settleStatus', 'SETTLED')
                    ->where('settleInviterId', $memberId)
                    ->where('tradeType', TRADE_TOKEN_RECHARGE),
            ], 'settleInviterMoney')
            ->withAttr([
                'trades_sum' => fn($value) => $value ?: '0',
                'settled_trades_sum' => fn($value) => $value ?: '0',
                // 'showName' => function ($value, $data) {
                //     $value = $data['type'] == 1 ? $data['realname'] : $data['nickname'];
                //     $length = mb_strlen($value) - 2;
                //     if ($length < 1) {
                //         $length = 1;
                //     }
                //     return substr_replace($value, '*', 1, $length);
                // },
            ])
            ->append(['headImg', 'headImgThumb', 'cropImg', 'showName'])
            ->hidden(['headImgUrl'])
            ->order('mi.created', 'DESC')
            ->limit($limit)
            ->select();
    }
}
